package com.edu.imau;

import com.edu.imau.FixedParking.Fixed;
import com.edu.imau.IC_CARD.IC;
import com.edu.imau.ManageUserInformation.User;
import com.edu.imau.ManagerSeat.Seat;
import com.edu.imau.ManagerRoleInformation.Manager;
import com.edu.imau.TParking.Temporary;

import java.sql.*;
import java.util.ArrayList;

public class ParkingDao {
    private Connection connection = ConnectionManager.getConnection();

    public ArrayList<User> getUserAll() {
        ArrayList<User> list = new ArrayList<User>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from usermanager";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                User user = new User(
                        resultSet.getInt("id"),
                        resultSet.getString("code"),
                        resultSet.getString("number"),
                        resultSet.getString("name"),
                        resultSet.getString("carcode"),
                        resultSet.getString("indate"),
                        resultSet.getString("outdate"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addUser(User user) {
        try {
            //3
            String sql = "insert into usermanager (code,number,name,carcode,indate,outdate)value (?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, user.getCode());
            statement.setString(2, user.getNumber());
            statement.setString(3, user.getName());
            statement.setString(4, user.getCarcode());
            statement.setString(5, user.getIndate());
            statement.setString(6, user.getOutdate());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteUser(int id) {
        try {
            //3
            String sql = "delete from usermanager where id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public User getUserById(int id) {
        User user = null;
        try {
            //3
            String sql = "select * from usermanager where id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                user = new User(
                        resultSet.getInt("id"),
                        resultSet.getString("code"),
                        resultSet.getString("number"),
                        resultSet.getString("name"),
                        resultSet.getString("carcode"),
                        resultSet.getString("indate"),
                        resultSet.getString("outdate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public int updateUser(User user) {
        try {
            //3
            String sql = "update usermanager set code = ?,number = ?,name = ?,carcode = ?,indate = ?,outdate = ?where id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, user.getCode());
            statement.setString(2, user.getNumber());
            statement.setString(3, user.getName());
            statement.setString(4, user.getCarcode());
            statement.setString(5, user.getIndate());
            statement.setString(6, user.getOutdate());
            statement.setInt(7, user.getId());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //-------------------------------------------------------------------------------------------------
    public ArrayList<Manager> getManagerAll() {
        ArrayList<Manager> list = new ArrayList<Manager>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from rolemanager";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                Manager manager = new Manager(
                        resultSet.getInt("role_id"),
                        resultSet.getString("role_code"),
                        resultSet.getString("role_virtual_name"),
                        resultSet.getString("role_name"),
                        resultSet.getString("role_true_name"),
                        resultSet.getString("role_password"),
                        resultSet.getString("role_contact"));
                list.add(manager);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addManager(Manager manager) {
        try {
            //3
            String sql = "insert into rolemanager (role_code,role_virtual_name,role_name,role_true_name,role_password,role_contact)value (?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, manager.getRole_code());
            statement.setString(2, manager.getRole_virtual_name());
            statement.setString(3, manager.getRole_name());
            statement.setString(4, manager.getRole_true_name());
            statement.setString(5, manager.getRole_password());
            statement.setString(6, manager.getRole_contact());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteManager(int role_id) {
        try {
            //3
            String sql = "delete from rolemanager where role_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, role_id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public Manager getManagerById(int role_id) {
        Manager manager = null;
        try {
            //3
            String sql = "select * from rolemanager where role_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, role_id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                manager = new Manager(
                        resultSet.getInt("role_id"),
                        resultSet.getString("role_code"),
                        resultSet.getString("role_virtual_name"),
                        resultSet.getString("role_name"),
                        resultSet.getString("role_true_name"),
                        resultSet.getString("role_password"),
                        resultSet.getString("role_contact"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return manager;
    }

    public int updateManger(Manager manager) {
        try {
            //3
            String sql = "update rolemanager set role_code = ?, role_virtual_name= ?,role_name = ?,role_true_name = ?,role_password = ?,role_contact = ?where role_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, manager.getRole_code());
            statement.setString(2, manager.getRole_virtual_name());
            statement.setString(3, manager.getRole_name());
            statement.setString(4, manager.getRole_true_name());
            statement.setString(5, manager.getRole_password());
            statement.setString(6, manager.getRole_contact());
            statement.setInt(7, manager.getRole_id());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //-----------------------------------------------------------------------------------
    public ArrayList<Seat> getSeatAll() {
        ArrayList<Seat> list = new ArrayList<Seat>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from car_seat";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                Seat seat = new Seat(
                        resultSet.getInt("seat_id"),
                        resultSet.getString("seat_code"),
                        resultSet.getString("seat_number"),
                        resultSet.getString("seat_section"),
                        resultSet.getString("seat_state"),
                        resultSet.getString("seat_tap"));
                list.add(seat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addSeat(Seat seat) {
        try {
            //3
            String sql = "insert into car_seat (seat_code,seat_number,seat_section,seat_state,seat_tap)value (?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, seat.getSeat_code());
            statement.setString(2, seat.getSeat_number());
            statement.setString(3, seat.getSeat_section());
            statement.setString(4, seat.getSeat_state());
            statement.setString(5, seat.getSeat_tap());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteSeat(int seat_id) {
        try {
            //3
            String sql = "delete from car_seat where seat_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, seat_id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    public Seat getSeatById(int seat_id) {
        Seat seat = null;
        try {
            //3
            String sql = "select * from car_seat where seat_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, seat_id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                seat = new Seat(
                        resultSet.getInt("seat_id"),
                        resultSet.getString("seat_code"),
                        resultSet.getString("seat_number"),
                        resultSet.getString("seat_section"),
                        resultSet.getString("seat_state"),
                        resultSet.getString("seat_tap"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return seat;
    }

    public int updateSeat(Seat seat) {
        try {
            //3
            String sql = "update car_seat set seat_code = ?, seat_number= ?,seat_section = ?,seat_state = ?,seat_tap = ?where seat_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, seat.getSeat_code());
            statement.setString(2, seat.getSeat_number());
            statement.setString(3, seat.getSeat_section());
            statement.setString(4, seat.getSeat_state());
            statement.setString(5, seat.getSeat_tap());
            statement.setInt(6, seat.getSeat_id());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //-----------------------------------------------------------------------------------IC处理
    public ArrayList<IC> getICAll() {
        ArrayList<IC> list = new ArrayList<>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from ic_card";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                IC ic = new IC(
                        resultSet.getInt("card_id"),
                        resultSet.getString("card_code"),
                        resultSet.getString("seat_number"),
                        resultSet.getString("name"),
                        resultSet.getString("gender"),
                        resultSet.getString("car_number"),
                        resultSet.getString("user_addr")
                );
                list.add(ic);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addIC(IC ic) {
        try {
            //3
            String sql = "insert into ic_card (card_code,seat_number, name, gender, car_number, user_addr)value (?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, ic.getCard_code());
            statement.setString(2, ic.getSeat_number());
            statement.setString(3, ic.getName());
            statement.setString(4, ic.getGender());
            statement.setString(5, ic.getCar_number());
            statement.setString(6, ic.getUser_addr());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteIC(int card_id) {
        try {
            //3
            String sql = "delete from ic_card where card_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, card_id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public IC getICById(int card_id) {
        IC ic = null;
        try {
            //3
            String sql = "select * from ic_card where card_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, card_id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                ic = new IC(
                        resultSet.getInt("card_id"),
                        resultSet.getString("card_code"),
                        resultSet.getString("seat_number"),
                        resultSet.getString("name"),
                        resultSet.getString("gender"),
                        resultSet.getString("car_number"),
                        resultSet.getString("user_addr"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ic;
    }

    public int updateIC(IC ic) {
        try {
            //3
            String sql = "update ic_card set card_code = ?, seat_number= ?,name = ?,gender = ?,car_number = ?,user_addr = ?where card_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, ic.getCard_code());
            statement.setString(2, ic.getSeat_number());
            statement.setString(3, ic.getName());
            statement.setString(4, ic.getGender());
            statement.setString(5, ic.getCar_number());
            statement.setString(6, ic.getUser_addr());
            statement.setInt(7, ic.getCard_id());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    // -------------------------------------------------------------------------固定停车

    public ArrayList<Fixed> getFixedAll() {
        ArrayList<Fixed> list = new ArrayList<Fixed>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from Fixed";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                Fixed fixed = new Fixed(
                        resultSet.getInt("fixed_id"),
                        resultSet.getString("fixed_name"),
                        resultSet.getString("fixed_age"),
                        resultSet.getString("fixed_brand"),
                        resultSet.getString("fixed_cnumber"),
                        resultSet.getString("fixed_pnumber"),
                        resultSet.getString("fixed_state"));
                list.add(fixed);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public int addFixed(Fixed fixed) {
        try {
            //3
            String sql = "insert into Fixed (fixed_name,fixed_age,fixed_brand,fixed_cnumber,fixed_pnumber,fixed_state)value (?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, fixed.getFixed_name());
            statement.setString(2, fixed.getFixed_age());
            statement.setString(3, fixed.getFixed_brand());
            statement.setString(4, fixed.getFixed_cnumber());
            statement.setString(5, fixed.getFixed_pnumber());
            statement.setString(6, fixed.getFixed_state());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int deleteFixed(int fixed_id) {
        try {
            //3
            String sql = "delete from Fixed where fixed_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, fixed_id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public Fixed getFixedById(int fixed_id) {
        Fixed fixed = null;
        try {
            //3
            String sql = "select * from Fixed where fixed_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, fixed_id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                fixed = new Fixed(
                        resultSet.getInt("fixed_id"),
                        resultSet.getString("fixed_name"),
                        resultSet.getString("fixed_age"),
                        resultSet.getString("fixed_brand"),
                        resultSet.getString("fixed_cnumber"),
                        resultSet.getString("fixed_pnumber"),
                        resultSet.getString("fixed_state"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fixed;
    }
    public int updateFixed(Fixed fixed) {
        try {
            //3
            String sql = "update Fixed set fixed_name = ?, fixed_age= ?,fixed_brand = ?,fixed_cnumber = ?,fixed_pnumber = ?,fixed_state = ?where fixed_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, fixed.getFixed_name());
            statement.setString(2, fixed.getFixed_age());
            statement.setString(3, fixed.getFixed_brand());
            statement.setString(4, fixed.getFixed_cnumber());
            statement.setString(5, fixed.getFixed_pnumber());
            statement.setString(6, fixed.getFixed_state());
            statement.setInt(7, fixed.getFixed_id());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // -------------------------------------------------------------------------临时停车
    public ArrayList<Temporary> getTemporaryAll() {
        ArrayList<Temporary> list = new ArrayList<Temporary>();
        try {
            //3
            Statement statement = connection.createStatement();
            String sql = "select * from TParking";
            ResultSet resultSet = statement.executeQuery(sql);
            //4
            while (resultSet.next()) {
                Temporary temporary = new Temporary(
                        resultSet.getInt("t_id"),
                        resultSet.getString("t_name"),
                        resultSet.getString("t_brand"),
                        resultSet.getString("t_cnumber"),
                        resultSet.getString("t_pnumber"),
                        resultSet.getString("t_btime"),
                        resultSet.getString("t_etime")
                );
                list.add(temporary);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public int addTemporary(Temporary temporary) {
        try {
            //3
            String sql = "insert into TParking (t_name,t_brand,t_cnumber,t_pnumber,t_btime,t_etime)value (?,?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, temporary.getT_name());
            statement.setString(2, temporary.getT_brand());
            statement.setString(3, temporary.getT_cnumber());
            statement.setString(4, temporary.getT_pnumber());
            statement.setString(5, temporary.getT_btime());
            statement.setString(6, temporary.getT_etime());
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int deleteTemporary(int t_id) {
        try {
            //3
            String sql = "delete from TParking where t_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, t_id);
            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public Temporary getTemporaryById(int t_id) {
        Temporary temporary = null;
        try {
            //3
            String sql = "select * from TParking where t_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, t_id);
            ResultSet resultSet = statement.executeQuery();
            //4
            if (resultSet.next()) {
                temporary = new Temporary(
                        resultSet.getInt("t_id"),
                        resultSet.getString("t_name"),
                        resultSet.getString("t_brand"),
                        resultSet.getString("t_cnumber"),
                        resultSet.getString("t_pnumber"),
                        resultSet.getString("t_btime"),
                        resultSet.getString("t_etime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return temporary;
    }
    public int updateTemporary(Temporary temporary) {
        try {
            //3
            String sql = "update TParking set t_name = ?, t_brand= ?,t_cnumber = ?,t_pnumber = ?,t_btime = ?,t_etime = ?where t_id =?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, temporary.getT_name());
            statement.setString(2, temporary.getT_brand());
            statement.setString(3, temporary.getT_cnumber());
            statement.setString(4, temporary.getT_pnumber());
            statement.setString(5, temporary.getT_btime());
            statement.setString(6, temporary.getT_etime());
            statement.setInt(7, temporary.getT_id());

            int resultSet = statement.executeUpdate();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
